package com.roc.web.action.merinfo;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.Utils;
import com.roc.util.constant.MerinfoStatus;
import com.roc.util.constant.Role;

public class MerinfoSearchCondtion extends BaseSearchCondition{

	
	private String orgCode;//机构号
	private String roleId; //角色 
	private long agentId;//所属代理商
	private String merchantNo;//商户号
	private String merchantName;//商户简称
	private String  merBrName; //注册名称
	private String status;//状态
	private int  merinfoFlag;//商户状态
	private String obj;//所有下级代理
	private  String  opId;//操作员id
	private  String rkopId;//风控操作员
	private String  minDate;//入网时间
	private String  maxDate;//入网时间
	private String  certifName;//法人
	private String  cardNo; //法人身份证
	private String  contactName;//业务联系人
	private String  phone;//联系电话
	private String  accountNo;//账号 
	private String  accountName;//账号 
	private String  riskGetMerType;//风控领取商户类型
	
	private String areaCode;//地区
	private String  mccName;//mcc
	private String  rateType;//扣率类型
	
	private String  midType;//商户类型
	
	private String  cycle;//结算周期
	private String  addr;//商户地址
	private String realName;//所属代理商中文
	private String groupCd;  //商户关联关系
	private String oneOrgCode; //机构号
	private String operationFlag; //运营标识
	private String preDirectMerReviewFlag; //直营预审核标识
	private String companyid; //分公司id
	private String oneAgentId;
	private String jgFlag;
	@Override
	protected String getOrderPartOfHQL() {
		if(Constance.isNotEmpty(status)&&(status.equals(MerinfoStatus.S_5.getCode()) || status.equals(MerinfoStatus.S_1.getCode())) && (roleId.equals(Role.RISK_CONTROL_OPERATORS.getCode()) || 
				   roleId.equals(Role.RISK_CONTROL_LEAD.getCode()))) {
				
			return "ORDER BY A.MCHT_CD ASC ";
		}else{
			return " ORDER BY A.REC_CRT_TS Desc  ";
		}
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer  sb= new StringBuffer();
		sb.append(" SELECT COUNT(1) ");
		sb.append("  from TBL_EDIT_MCHT_INF a, TBL_EDIT_MCHT_BANKACCOUNT D, TBL_INS_INF TI    \n");   
		sb.append(" where a.mcht_cd = d.owner_cd                                              \n");   
		sb.append("   and a.bank_belong_cd = ti.ins_id_cd                                     \n"); 
		if(Constance.isNotEmpty(preDirectMerReviewFlag)){ 
			if (roleId.equals(Role.OPERATORS_LEAD.getCode())){
				sb.append("   and a.group_cd='2' and a.status='14' ");
			}else if(jgFlag.equals("40001")){
				 sb.append("   and a.group_cd='2' and a.status='14'  and a.uc_bc_cd_area in (select o.org_code from tbl_org_main o where o.area_id=").append(companyid).append("  ) \n");  
					
			}else{
				 sb.append("   and a.group_cd='2' and a.status='14'  and a.uc_bc_cd_area=").append(oneAgentId).append(" \n");  
			}
			
		}else{
			//登录人角色销售人员                   
			if(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode())){        
				sb.append(" and exists (select 1 from t_oper_role ar  where ar.agentid = a.uc_bc_cd_area and ar.operid =").append(opId).append(")");
			}
			//角色为代理商或商服宝会员或父级id为代理商            
			if(roleId.equals(Role.AGENT.getCode())||roleId.equals(Role.SFB_AGENT.getCode())){
				sb.append("   and a.ext1 in (SELECT distinct to_char(o.id)    \n");   
				sb.append("                    from T_OPERATORS o                                     \n");   
				sb.append("                   where o.role_type in ('02','03')  START WITH o.id = ").append(opId).append(" \n");   
			    sb.append("                  CONNECT BY o.supri_id = PRIOR o.id)                      \n");   
			}
		}
		return sb.toString();
	}

	 
	@Override
	protected String getSearchHQL() {
		StringBuffer  sb= new StringBuffer();
		
		sb.append(" SELECT A.MCHT_CD,A.NAME_BUSI,A.NAME MERNAME,TI.INS_NAME, A.CONTACT_NAME,");
		sb.append(" A.CONTACT_PHONENO,A.CERTIF,A.CERTIF_NO,A.BUSI_LICE_NO,A.REG_ADDR,A.STATUS,");
		sb.append("  (select o2.name                                                                                 \n");
		sb.append("           from tbl_area o2                                                                         \n");
		sb.append("          where o2.id =                                                                       \n");
		sb.append("                (select o1.companyid from T_OPERATORS o1 where to_char(o1.id) = a.ext1) and rownum=1) COMPANYNAME,  \n");
		sb.append("        (select o2.real_name                                                                           \n");
		sb.append("           from T_OPERATORS o2                                                                         \n");
		sb.append("          where to_char(o2.id) =                                                                       \n");
		sb.append("                (select o1.oneagentid from T_OPERATORS o1 where to_char(o1.id) = a.ext1)) ONEAGENGNAME,\n");
		sb.append("        (select o1.real_name from T_OPERATORS o1 where to_char(o1.id) = a.ext1) REAL_NAME,             \n");
		sb.append(" D.NAME ACCOUNTNAME,D.ACCOUNT,D.BANK_NAME,D.BANK_CODE,");
		if(null!=status && status.equals(MerinfoStatus.S_5.getCode())){//审核查询
			if((roleId.equals(Role.RISK_CONTROL_LEAD.getCode()) || roleId.equals(Role.RISK_CONTROL_OPERATORS.getCode()) || roleId.equals(Role.SYSTEM.getCode())) && merinfoFlag==3){
			sb.append("  (select TR1.OP_ACCOUNTNAME                           \n");
			sb.append("           from T_RECEIVE tr1                          \n");
			sb.append("          where TR1.STATUS = '0'                       \n");
			sb.append("            AND A.MCHT_CD = TR1.MERNO) OP_ACCOUNTNAME, \n");
		  }
		}
		sb.append(" NVL2((SELECT DISTINCT PROD_CD FROM TBL_EDIT_MCHT_BIZ_DEAL t WHERE t.MCHT_CD = A.MCHT_CD AND t.PROD_CD ='1000'),'T1',''),");
		sb.append(" NVL2((SELECT DISTINCT PROD_CD FROM TBL_EDIT_MCHT_BIZ_DEAL t WHERE t.MCHT_CD = A.MCHT_CD AND t.PROD_CD ='1171'),'T0','')");
		sb.append(",A.GROUP_CD,A.NATION_CD,A.ISGROUP,A.UP_MCC_CD,a.ext1, ");
		sb.append(" NVL2((SELECT DISTINCT PROD_CD FROM TBL_EDIT_MCHT_BIZ_DEAL t WHERE t.MCHT_CD = A.MCHT_CD AND t.PROD_CD ='3000'),'直连T1',''),");
		sb.append(" NVL2((SELECT DISTINCT PROD_CD FROM TBL_EDIT_MCHT_BIZ_DEAL t WHERE t.MCHT_CD = A.MCHT_CD AND t.PROD_CD ='1271'),'直连T0','')");
		sb.append(" ,a.rec_upd_opr");
		sb.append("  from TBL_EDIT_MCHT_INF a, TBL_EDIT_MCHT_BANKACCOUNT D, TBL_INS_INF TI    \n");   
		sb.append(" where a.mcht_cd = d.owner_cd                                              \n");   
		sb.append("   and a.bank_belong_cd = ti.ins_id_cd                                     \n"); 
		if(Constance.isNotEmpty(preDirectMerReviewFlag)){ 
			if (roleId.equals(Role.OPERATORS_LEAD.getCode())){
				sb.append("   and a.group_cd='2' and a.status='14' ");
			}else if(jgFlag.equals("40001")){
				 sb.append("   and a.group_cd='2' and a.status='14'  and a.uc_bc_cd_area in (select o.org_code from tbl_org_main o where o.area_id=").append(companyid).append("  ) \n");  
					
			}else{
				 sb.append("   and a.group_cd='2' and a.status='14'  and a.uc_bc_cd_area=").append(oneAgentId).append(" \n");  
			}
		}else{
			//登录人角色销售人员                   
			if(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode())){        
				sb.append(" and exists (select 1 from t_oper_role ar  where ar.agentid = a.uc_bc_cd_area and ar.operid =").append(opId).append(")");
			}
			//角色为代理商或商服宝会员或父级id为代理商            
			if(roleId.equals(Role.AGENT.getCode())||roleId.equals(Role.SFB_AGENT.getCode())){
				sb.append("   and a.ext1 in (SELECT distinct to_char(o.id)    \n");   
				sb.append("                    from T_OPERATORS o                                     \n");   
				sb.append("                   where o.role_type in ('02','03')  START WITH o.id = ").append(opId).append(" \n");   
			    sb.append("                  CONNECT BY o.supri_id = PRIOR o.id)                      \n");   
			}
		}
		return sb.toString();
	}

	 
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		
		//所属机构
		if(Constance.isNotEmpty(orgCode)){ 
			filedBeanList.add(new WhereHQLFieldBean("A.BANK_BELONG_CD", orgCode.trim(), "=", false));
		}
		 
		if(merinfoFlag>0){ 
			if(merinfoFlag==1){//进件中
				status=MerinfoStatus.S_1.getCode();
			}else if(merinfoFlag==2){//驳回
				status=MerinfoStatus.S_4.getCode();
			}else if(merinfoFlag==3){//领件（审核查询）
				status=MerinfoStatus.S_5.getCode();
			}else if(merinfoFlag==4){//间连直营进件中
				status=MerinfoStatus.S_14.getCode();
			}else if(merinfoFlag==5){//间连直营驳回
				status=MerinfoStatus.S_15.getCode();
			}else if(merinfoFlag==98){
				status="98";
			}
		}
		if(null!=status && status.equals(MerinfoStatus.S_4.getCode())){// 驳回查询
			filedBeanList.add(new WhereHQLFieldBean(" A.STATUS ='"+MerinfoStatus.S_4.getCode()+"'"));
		}else if(null!=status && status.equals(MerinfoStatus.S_5.getCode())){//审核查询
			if((roleId.equals(Role.RISK_CONTROL_LEAD.getCode()) || roleId.equals(Role.RISK_CONTROL_OPERATORS.getCode()) )&& merinfoFlag==3){
				filedBeanList.add(new WhereHQLFieldBean(" A.STATUS ='"+MerinfoStatus.S_5.getCode()+"'  AND EXISTS (SELECT TR.MERNO FROM T_RECEIVE TR WHERE TR.OP_ID ="+rkopId.trim()+" AND TR.STATUS='0' AND  TR.MERNO = A.MCHT_CD)"));
			}else{
				filedBeanList.add(new WhereHQLFieldBean(" A.STATUS ='"+MerinfoStatus.S_5.getCode()+"' "));
			}
		}else if(null!=status && status.equals(MerinfoStatus.S_1.getCode())){//领件查询(直连、结算账户对公)
			filedBeanList.add(new WhereHQLFieldBean(" A.STATUS ='"+MerinfoStatus.S_1.getCode()+"'  "));
			
			if(null != riskGetMerType && riskGetMerType.trim().equals("1")){ //直联商户
				
				filedBeanList.add(new WhereHQLFieldBean(" A.group_cd ='1'  "));
				
			}else if(null != riskGetMerType && riskGetMerType.trim().equals("2")){//优惠类MCC
				
				String mcc_stop=Utils.getConfigValue("MCC_STOP");//不需要审核的mcc
				filedBeanList.add(new WhereHQLFieldBean(" A.UP_MCC_CD IN (  "+mcc_stop+(")")));
			}else if(null != riskGetMerType && riskGetMerType.trim().equals("3")){//结算账户对公
				
				filedBeanList.add(new WhereHQLFieldBean(" A.APPROVALUSERNAME='1'  "));
			}else if(null != riskGetMerType && riskGetMerType.trim().equals("4")){//间联/普通MCC/结算账户对私
				
				String mcc_stop=Utils.getConfigValue("MCC_STOP");//不需要审核的mcc
				filedBeanList.add(new WhereHQLFieldBean(" a.group_CD='0' and a.APPROVALUSERNAME='0'  and a.UP_MCC_CD not in ("+mcc_stop+")  "));
			}else if(null != riskGetMerType && riskGetMerType.trim().equals("5")){//间联直营商户 
				
				filedBeanList.add(new WhereHQLFieldBean(" A.group_cd ='2'  "));   
			}
		}else if(null!=status && status.equals(MerinfoStatus.S_14.getCode())){// 间连直营进件中
			filedBeanList.add(new WhereHQLFieldBean(" A.STATUS ='"+MerinfoStatus.S_14.getCode()+"'"));	
		}else if(null!=status && status.equals(MerinfoStatus.S_15.getCode())){// 间连直营驳回
			filedBeanList.add(new WhereHQLFieldBean(" A.STATUS ='"+MerinfoStatus.S_15.getCode()+"'"));
		}else if(null!=status && status.equals("98")){// 直营待审核
			filedBeanList.add(new WhereHQLFieldBean(" A.STATUS IN ('"+MerinfoStatus.S_14.getCode()+"','"+MerinfoStatus.S_15.getCode()+"')"));
		}else{//待审核查询（所有状态）
			filedBeanList.add(new WhereHQLFieldBean(" A.STATUS IN('"+MerinfoStatus.S_1.getCode()+"','"+MerinfoStatus.S_4.getCode()+"','"+MerinfoStatus.S_5.getCode()+"')"));
		}
		
		if(Constance.isNotEmpty(merchantNo)){ //商户号
			filedBeanList.add(new WhereHQLFieldBean("A.MCHT_CD", merchantNo.trim(), "=", false));
		}
		
		//机构
		if(Constance.isNotEmpty(oneOrgCode)&&!roleId.equals(Role.SYSTEM.getCode())&&!roleId.equals(Role.AGENT.getCode())&&!(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode()))){//机构
			filedBeanList.add(new WhereHQLFieldBean("A.uc_bc_cd_area", oneOrgCode.trim(), "=", false));
        }
		 
		if(Constance.isNotEmpty(merBrName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME", merBrName.trim(), "like", false,"ALL"));
		}
		
		if(Constance.isNotEmpty(merchantName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME_BUSI", merchantName.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ //入网时间
			filedBeanList.add(new WhereHQLFieldBean("A.APPL_DATE",minDate,">=", false));
       	    filedBeanList.add(new WhereHQLFieldBean("A.APPL_DATE",maxDate,"<=", false));
		}
		if(Constance.isNotEmpty(opId) 
				&& Constance.isNotEmpty(roleId)  
				&& roleId.equals(Role.AGENT_BUSS.getCode())){//操作员 
	    	filedBeanList.add(new WhereHQLFieldBean("A.REC_UPD_OPR", opId.trim(), "=", false));
	    }
		if(Constance.isNotEmpty(certifName)){ //法人
			filedBeanList.add(new WhereHQLFieldBean("A.CERTIF", certifName.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(cardNo)){ //法人身份证号
			filedBeanList.add(new WhereHQLFieldBean("A.CERTIF_NO", cardNo.trim(), "=", false));
		}
		if(Constance.isNotEmpty(contactName)){ //业务联系人
			filedBeanList.add(new WhereHQLFieldBean("A.CONTACT_NAME", contactName.trim() ,"like", false,"ALL"));
		} 
		if(Constance.isNotEmpty(phone)){ //业务联系电话
			filedBeanList.add(new WhereHQLFieldBean("A.CONTACT_PHONENO", phone.trim(), "=", false));
		} 
		if(Constance.isNotEmpty(accountNo)){ //结算账号
			filedBeanList.add(new WhereHQLFieldBean("D.ACCOUNT", accountNo.trim(), "=", false));
		} 
		if(Constance.isNotEmpty(accountName)){ //结算账户名称
			filedBeanList.add(new WhereHQLFieldBean("D.NAME", accountName.trim() ,"like", false,"ALL"));
		}
		
		
		
		if(Constance.isNotEmpty(areaCode)){ //地区
			if(!areaCode.equals("99")){
				filedBeanList.add(new WhereHQLFieldBean("em.area_code", areaCode.trim(), "=", false));
			}
			
		}
		//商户关联关系
	    if(Constance.isNotEmpty(groupCd)&&!"99".equals(groupCd)){//商户关联关系
			filedBeanList.add(new WhereHQLFieldBean("A.GROUP_CD", groupCd.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(realName)){//代理商中文(模糊)
			filedBeanList.add(new WhereHQLFieldBean("op.REAL_NAME", realName.trim(), "like", false,"ALL"));
		}
        /*if(agentId>0){//代理商
        	filedBeanList.add(new WhereHQLFieldBean("em.AGENT_ID", agentId, "=", false));
        }*/
		return filedBeanList;
	}


	public String getMerchantNo() {
		return merchantNo;
	}

	public void setMerchantNo(String merchantNo) {
		this.merchantNo = merchantNo;
	}

	public String getMerchantName() {
		return merchantName;
	}

	public void setMerchantName(String merchantName) {
		this.merchantName = merchantName;
	}

	public String getAreaCode() {
		return areaCode;
	}

	public void setAreaCode(String areaCode) {
		this.areaCode = areaCode;
	}


	public String getMerBrName() {
		return merBrName;
	}

	public void setMerBrName(String merBrName) {
		this.merBrName = merBrName;
	}

	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	

	public String getMccName() {
		return mccName;
	}

	public void setMccName(String mccName) {
		this.mccName = mccName;
	}

	public String getRateType() {
		return rateType;
	}

	public void setRateType(String rateType) {
		this.rateType = rateType;
	}

	

	public String getMidType() {
		return midType;
	}

	public void setMidType(String midType) {
		this.midType = midType;
	}

	
	public String getCycle() {
		return cycle;
	}

	public void setCycle(String cycle) {
		this.cycle = cycle;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public long getAgentId() {
		return agentId;
	}

	public void setAgentId(long agentId) {
		this.agentId = agentId;
	}


	public void setMerinfoFlag(int merinfoFlag) {
		this.merinfoFlag = merinfoFlag;
	}

	public int getMerinfoFlag() {
		return merinfoFlag;
	}

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public String getRoleId() {
		return roleId;
	}

	public void setRoleId(String roleId) {
		this.roleId = roleId;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getRkopId() {
		return rkopId;
	}

	public void setRkopId(String rkopId) {
		this.rkopId = rkopId;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}


	public String getContactName() {
		return contactName;
	}

	public void setContactName(String contactName) {
		this.contactName = contactName;
	}

	public String getCertifName() {
		return certifName;
	}

	public void setCertifName(String certifName) {
		this.certifName = certifName;
	}

	public String getAccountNo() {
		return accountNo;
	}

	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public String getRiskGetMerType() {
		return riskGetMerType;
	}

	public void setRiskGetMerType(String riskGetMerType) {
		this.riskGetMerType = riskGetMerType;
	}

	public String getGroupCd() {
		return groupCd;
	}

	public void setGroupCd(String groupCd) {
		this.groupCd = groupCd;
	}

	public String getOneOrgCode() {
		return oneOrgCode;
	}

	public void setOneOrgCode(String oneOrgCode) {
		this.oneOrgCode = oneOrgCode;
	}

	public String getOperationFlag() {
		return operationFlag;
	}

	public void setOperationFlag(String operationFlag) {
		this.operationFlag = operationFlag;
	}

	public String getPreDirectMerReviewFlag() {
		return preDirectMerReviewFlag;
	}

	public void setPreDirectMerReviewFlag(String preDirectMerReviewFlag) {
		this.preDirectMerReviewFlag = preDirectMerReviewFlag;
	}

	public String getCompanyid() {
		return companyid;
	}

	public void setCompanyid(String companyid) {
		this.companyid = companyid;
	}

	public String getOneAgentId() {
		return oneAgentId;
	}

	public void setOneAgentId(String oneAgentId) {
		this.oneAgentId = oneAgentId;
	}

	public String getJgFlag() {
		return jgFlag;
	}

	public void setJgFlag(String jgFlag) {
		this.jgFlag = jgFlag;
	}
	
    
	
}
